﻿
using MS.InformationManagement.DAL.RecordCenter;
using Microsoft.SharePoint;
using System.Linq;
using System.Collections.Generic;
using Microsoft.SharePoint.WebControls;
using System.Text;
using MS.InformationManagement.Support;
using MS.InformationManagement.BLL.TRD;
using System;
using System.Data;
using MS.InformationManagement.Support.Constants;
namespace MS.InformationManagement.BLL.RecordCenter
{
    public class TRDBLL
    {
        public static IEnumerable<FondosFondo> LoadParentBackdrops()
        {
            IEnumerable<FondosFondo> foundDropbacks = null;
            using (DataContext dx = new DataContext(SPContext.Current.Web.Url))
            {
                foundDropbacks = (from fondo in dx.Fondos
                                  where fondo.FondoAlQuePertenece == null
                                  orderby fondo.Title ascending
                                  select fondo);
            }
            return foundDropbacks;
        }

        public static IEnumerable<FondosFondo> LoadAllBackdrops()
        {
            IEnumerable<FondosFondo> foundDropbacks = null;
            using (DataContext dx = new DataContext(SPContext.Current.Web.Url))
            {
                foundDropbacks = (from fondo in dx.Fondos
                                  orderby fondo.Title ascending
                                  select fondo);
            }
            return foundDropbacks;
        }

        public static IEnumerable<FondosFondo> LoadBackdropChilds(int parentDropbackID)
        {
            IEnumerable<FondosFondo> foundDropbacks = null;
            using (DataContext dx = new DataContext(SPContext.Current.Web.Url))
            {
                foundDropbacks = (from fondo in dx.Fondos
                                  where fondo.FondoAlQuePertenece.Id.Equals(parentDropbackID)
                                  orderby fondo.Title ascending
                                  select fondo);
            }
            return foundDropbacks;
        }

        public static IEnumerable<SeccionesSeccion> LoadSections()
        {
            IEnumerable<SeccionesSeccion> foundSections = null;
            using (DataContext dx = new DataContext(SPContext.Current.Web.Url))
            {
                foundSections = (from seccion in dx.Secciones
                                 orderby seccion.Title ascending
                                 select seccion);
            }
            return foundSections;
        }

        public static IEnumerable<SeccionesSeccion> LoadSubsections(int sectionID)
        {
            IEnumerable<SeccionesSeccion> foundSubsections;
            using (DataContext dx = new DataContext(SPContext.Current.Web.Url))
            {
                foundSubsections = (from subseccion in dx.Secciones
                                    where subseccion.SecciónPadre.Id.Equals(sectionID)
                                    orderby subseccion.Title ascending
                                    select subseccion);
            }
            return foundSubsections;
        }

        public static List<Serie> LoadSeries()
        {
            IEnumerable<Serie> foundSeries = null;
            using (DataContext dx = new DataContext(SPContext.Current.Web.Url))
            {
                foundSeries = (from serie in dx.Series
                               orderby serie.Title ascending
                               select serie);
            }
            return foundSeries.ToList();
        }

        public static List<Subserie> LoadSubseries(int serieID)
        {
            IEnumerable<Subserie> foundSubseries = null;
            using (DataContext dx = new DataContext(SPContext.Current.Web.Url))
            {
                foundSubseries = (from subserie in dx.Subseries
                                  where subserie.Serie.Id.Equals(serieID)
                                  orderby subserie.Title ascending
                                  select subserie);
            }

            return foundSubseries.ToList();
        }

        public static List<TipoDocumental> LoadDocumentTypes()
        {
            IEnumerable<TipoDocumental> foundDocumentTypes = null;
            using (DataContext dx = new DataContext(SPContext.Current.Web.Url))
            {
                foundDocumentTypes = (from documentType in dx.TiposDocumentales
                                      orderby documentType.Title ascending
                                      select documentType);
            }
            return foundDocumentTypes.ToList();
        }

        public static FondosFondo GetFondo(int ID)
        {
            FondosFondo found = null;
            using (DataContext dx = new DataContext(SPContext.Current.Web.Url))
            {
                found = dx.Fondos.SingleOrDefault(fondo => fondo.Id.Equals(ID));
            }
            return found;
        }

        public static SeccionesSeccion GetSeccion(int ID)
        {
            SeccionesSeccion found = null;
            using (DataContext dx = new DataContext(SPContext.Current.Web.Url))
            {
                found = dx.Secciones.SingleOrDefault(seccion => seccion.Id.Equals(ID));
            }
            return found;
        }

        public static SeccionesSeccion GetSubseccion(int ID)
        {
            SeccionesSeccion found = null;
            using (DataContext dx = new DataContext(SPContext.Current.Web.Url))
            {
                found = dx.Secciones.SingleOrDefault(subseccion => subseccion.Id.Equals(ID));
            }
            return found;
        }

        public static void AddTRDConfigItem(string title, FondosFondo backdrop, FondosFondo backdropChild, SeccionesSeccion section
            , SeccionesSeccion subsection, Serie serie, Subserie subserie, double generalStorageTime, double centralStorageTime, bool shouldbeKept, bool shouldbeSelected
            , bool shouldbeDeleted, bool ShouldbeTechnicalMean, string law,
            List<TipoDocumental> documentTypes)
        {
            //ItemTRD newItem = null;

            //using (DataContext dx = new DataContext(SPContext.Current.Web.Url))
            //{
            //    dx.Fondos.Attach(backdrop);
            //    if (backdropChild != null)
            //        dx.Fondos.Attach(backdropChild);
            //    dx.Secciones.Attach(section);
            //    dx.Secciones.Attach(subsection);
            //    dx.Series.Attach(serie);
            //    dx.Subseries.Attach(subserie);

            //    newItem = new ItemTRD
            //    {
            //        Title = title,
            //        Conservación = shouldbeKeept,
            //        Eliminación = shouldbeDeleted,
            //        Fondo = backdrop,
            //        Legislación = law,
            //        MedioDeConservación = ShouldbeTechnicalMean,
            //        SeccionTRD = section,
            //        Selección = shouldbeSelected,
            //        SerieTRD = serie,
            //        SubFondo = backdropChild,
            //        SubseccionTRD = subsection,
            //        SubserieTRD = subserie,
            //        TiempoDeRetenciónEnArchivoCentral = centralStorageTime,
            //        TiempoDeRetenciónEnArchivoGeneral = generalStorageTime
            //        //TiposDocumentales = (Microsoft.SharePoint.Linq.EntitySet<TipoDocumental>)documentTypes
            //    };

            //    newItem.TiposDocumentales = new Microsoft.SharePoint.Linq.EntitySet<TipoDocumental>();
            //    foreach (TipoDocumental currentDocumentType in documentTypes)
            //    {
            //        dx.TiposDocumentales.Attach(currentDocumentType);
            //        newItem.TiposDocumentales.Add(currentDocumentType);
            //    }


            //    dx.ConfiguraciónTablaDeRetenciónDocumental.InsertOnSubmit(newItem);
            //    dx.SubmitChanges();
            //}
            //return newItem;
            using (DataContext ctx = new DataContext(SPContext.Current.Web.Url))
            {
                CreateTRDItemCommand cCommand = new CreateTRDItemCommand(title, backdrop, backdropChild, section, subsection, serie, subserie, generalStorageTime, centralStorageTime,
                    shouldbeKept, shouldbeSelected, shouldbeDeleted, ShouldbeTechnicalMean, law, documentTypes, ctx);
                cCommand.Execute();
            }
        }

        public static void UpdateTRDConfigItem(int? id, string title, FondosFondo backdrop, FondosFondo backdropChild, SeccionesSeccion section
            , SeccionesSeccion subsection, Serie serie, Subserie subserie, double generalStorageTime, double centralStorageTime, bool shouldbeKept, bool shouldbeSelected
            , bool shouldbeDeleted, bool ShouldbeTechnicalMean, string law,
            List<TipoDocumental> documentTypes, string changeNotes)
        {
            using (DataContext ctx = new DataContext(SPContext.Current.Web.Url))
            {
                UpdateTRDItemCommand uCommand = new UpdateTRDItemCommand(id, title, backdrop, backdropChild, section, subsection, serie, subserie, generalStorageTime, centralStorageTime,
                   shouldbeKept, shouldbeSelected, shouldbeDeleted, ShouldbeTechnicalMean, law, documentTypes, changeNotes, ctx);
                uCommand.Execute();

            }

        }

        public static ItemTRD GetItemTRD(int? id)
        {
            ItemTRD entity = null;
            using (DataContext ctx = new DataContext(SPContext.Current.Web.Url))
            {
                entity = ctx.ConfiguraciónTablaDeRetenciónDocumental.SingleOrDefault(item => item.Id.Equals(id));
                if (entity == default(ItemTRD)) { throw new Exception(string.Format("No existe un item en la lista de configuración de tablas de retención documental con el id {0}", id)); }
            }
            return entity;
        }

        public static object GetTRDConfigItems(FondosFondo backdrop, FondosFondo backdropChild, SeccionesSeccion section, SeccionesSeccion subsection, int pageIndex, int pageSize, out int totalRecords)
        {
            int startRow = pageIndex * pageSize;
            IEnumerable<ItemTRD> elements = null;

            using (DataContext ctx = new DataContext(SPContext.Current.Web.Url))
            {
                elements = ctx.ConfiguraciónTablaDeRetenciónDocumental.Where(item => item.Activo.Equals(true)).ToList();
                if (backdrop != null) { elements = elements.Where(item => item.Fondo.Id.Equals(backdrop.Id)); }
                if (backdropChild != null) { elements = elements.Where(item => item.SubFondo.Id.Equals(backdropChild.Id)); }
                if (section != default(SeccionesSeccion)) { elements = elements.Where(item => item.SeccionTRD.Id.Equals(section.Id)); }
                if (subsection != default(SeccionesSeccion)) { elements = elements.Where(item => item.SubseccionTRD.Id.Equals(subsection.Id)); }
                totalRecords = elements.Count();
                elements = elements.Skip(startRow).Take(pageSize);
            }
            return elements;
        }

        public static IEnumerable<ItemTRD> GetHistoricItems(FondosFondo backdrop, FondosFondo backdropChild, SeccionesSeccion section, SeccionesSeccion subsection, int pageIndex, int pageSize, out int totalRecords)
        {
            int startRow = pageIndex * pageSize;
            IEnumerable<ItemTRD> elements = null;

            using (DataContext ctx = new DataContext(SPContext.Current.Web.Url))
            {
                elements = ctx.ConfiguraciónTablaDeRetenciónDocumental.OrderByDescending(item => item.Created).ToList();
                if (backdrop != null) { elements = elements.Where(item => item.Fondo.Id.Equals(backdrop.Id)); }
                if (backdropChild != null) { elements = elements.Where(item => item.SubFondo.Id.Equals(backdropChild.Id)); }
                if (section != default(SeccionesSeccion)) { elements = elements.Where(item => item.SeccionTRD.Id.Equals(section.Id)); }
                if (subsection != default(SeccionesSeccion)) { elements = elements.Where(item => item.SubseccionTRD.Id.Equals(subsection.Id)); }
                totalRecords = elements.Count();
                elements = elements.Skip(startRow).Take(pageSize);
            }

            return elements;
        }

        public static bool CanAddTRDConfigItem(FondosFondo backdrop, FondosFondo backdropChild, SeccionesSeccion section, SeccionesSeccion subsection, Serie serie,
            Subserie subserie)
        {

            bool validOperation = true;
            int elementCount = 0;
            using (DataContext dx = new DataContext(SPContext.Current.Web.Url))
            {
                if (backdropChild != null)
                {
                    var elements = dx.ConfiguraciónTablaDeRetenciónDocumental.Where(item => item.Fondo.Id.Equals(backdrop.Id)
                            && item.SubFondo.Id.Equals(backdropChild.Id)
                            && item.SeccionTRD.Id.Equals(section.Id)
                            && item.SubseccionTRD.Id.Equals(subsection.Id)
                            && item.SerieTRD.Id.Equals(serie.Id)
                            && item.SubserieTRD.Id.Equals(subserie.Id));
                    elementCount = elements.Count();
                }
                else
                {
                    var elements = dx.ConfiguraciónTablaDeRetenciónDocumental.Where(item => item.Fondo.Id.Equals(backdrop.Id)
                            && item.SeccionTRD.Id.Equals(section.Id)
                            && item.SubseccionTRD.Id.Equals(subsection.Id)
                            && item.SerieTRD.Id.Equals(serie.Id)
                            && item.SubserieTRD.Id.Equals(subserie.Id));
                    elementCount = elements.Count();
                }
                validOperation = elementCount == 0;
            }
            return validOperation;
        }

        public static IEnumerable<SeccionesSeccion> GetPendingSections(int pageIndex, int pageSize, out int totalRecords)
        {
            int startRow = pageIndex * pageSize;
            IEnumerable<SeccionesSeccion> nonUsedEntities = null;
            IEnumerable<SeccionesSeccion> usedEntities = null;
            IEnumerable<SeccionesSeccion> usedEntitiesSections = null;
            IEnumerable<SeccionesSeccion> usedEntitiesSubsections = null;

            using (DataContext ctx = new DataContext(SPContext.Current.Web.Url))
            {
                usedEntitiesSections = from item in
                                           (from itemtrd in ctx.ConfiguraciónTablaDeRetenciónDocumental
                                            where itemtrd.Activo.Equals(true)
                                            select itemtrd).ToList()
                                       select item.SeccionTRD;
                usedEntitiesSubsections = from item in
                                              (from itemtrd in ctx.ConfiguraciónTablaDeRetenciónDocumental
                                               where itemtrd.Activo.Equals(true)
                                               select itemtrd).ToList()
                                          select item.SubseccionTRD;
                usedEntities = usedEntitiesSections.Union(usedEntitiesSubsections);
                usedEntities = usedEntities.Distinct();
                nonUsedEntities = ctx.Secciones.Where(item => item.SecciónPadre != null).ToList().Except(usedEntities).OrderBy(item => item.Title);

                totalRecords = nonUsedEntities.Count();
                nonUsedEntities = nonUsedEntities.Skip(startRow).Take(pageSize);
            }
            return nonUsedEntities;
        }

        public static DataTable GetPendingSectionsExportData()
        {
            DataTable exportData = null;
            IEnumerable<SeccionesSeccion> nonUsedEntities = null;
            IEnumerable<SeccionesSeccion> usedEntities = null;
            IEnumerable<SeccionesSeccion> usedEntitiesSections = null;
            IEnumerable<SeccionesSeccion> usedEntitiesSubsections = null;

            using (DataContext ctx = new DataContext(SPContext.Current.Web.Url))
            {
                usedEntitiesSections = from item in
                                           (from itemtrd in ctx.ConfiguraciónTablaDeRetenciónDocumental
                                            where itemtrd.Activo.Equals(true)
                                            select itemtrd).ToList()
                                       select item.SeccionTRD;
                usedEntitiesSubsections = from item in
                                              (from itemtrd in ctx.ConfiguraciónTablaDeRetenciónDocumental
                                               where itemtrd.Activo.Equals(true)
                                               select itemtrd).ToList()
                                          select item.SubseccionTRD;
                usedEntities = usedEntitiesSections.Union(usedEntitiesSubsections);
                usedEntities = usedEntities.Distinct();
                nonUsedEntities = ctx.Secciones.Where(item => item.SecciónPadre != null).ToList().Except(usedEntities).OrderBy(item => item.Title);
            }

            exportData = new DataTable();
            exportData.Columns.Add("Codigo");
            exportData.Columns.Add("Seccion");
            exportData.Columns.Add("Subseccion");
            exportData.Columns.Add("Usuario Encargado Correspondencia");

            foreach (var entity in nonUsedEntities)
            {
                exportData.Rows.Add(new object[] { entity.Código, entity.SecciónPadre.Title, entity.Title, entity.UsuarioEncargadoCorrespondencia });
            }

            return exportData;
        }

        public static DataTable GetHistoricItemsExportData(FondosFondo backdrop, FondosFondo backdropChild, SeccionesSeccion section, SeccionesSeccion subsection)
        {
            DataTable exportData = null;
            IEnumerable<ItemTRD> elements = null;

            using (DataContext ctx = new DataContext(SPContext.Current.Web.Url))
            {
                elements = ctx.ConfiguraciónTablaDeRetenciónDocumental.OrderByDescending(item => item.Created).ToList();
                if (backdrop != null) { elements = elements.Where(item => item.Fondo.Id.Equals(backdrop.Id)); }
                if (backdropChild != null) { elements = elements.Where(item => item.SubFondo.Id.Equals(backdropChild.Id)); }
                if (section != default(SeccionesSeccion)) { elements = elements.Where(item => item.SeccionTRD.Id.Equals(section.Id)); }
                if (subsection != default(SeccionesSeccion)) { elements = elements.Where(item => item.SubseccionTRD.Id.Equals(subsection.Id)); }
            }

            exportData = new DataTable();
            exportData.Columns.Add("Fecha");
            exportData.Columns.Add("Administrador");
            exportData.Columns.Add("Serie");
            exportData.Columns.Add("SubSerie");
            exportData.Columns.Add("Operacion Realizada");
            exportData.Columns.Add("Observaciones");

            foreach (var entity in elements)
            {
                exportData.Rows.Add(
                    new object[] 
                    { 
                        entity.Created.ToString("dd/MM/yyyy hh:mm:ss"), entity.Author, entity.SerieTRD.Title, entity.SubserieTRD.Title, entity.OperaciónRealizada.Replace("\n", " ")
                        , entity.Observaciones 
                    });
            }

            return exportData;
        }

        public static DataTable GetTRDConfigItemsExportData(FondosFondo backdrop, FondosFondo backdropChild, SeccionesSeccion section, SeccionesSeccion subsection)
        {
            IEnumerable<ItemTRD> elements = null;
            DataTable exportData = null;

            using (DataContext ctx = new DataContext(SPContext.Current.Web.Url))
            {
                elements = ctx.ConfiguraciónTablaDeRetenciónDocumental.Where(item => item.Activo.Equals(true)).ToList();
                if (backdrop != null) { elements = elements.Where(item => item.Fondo.Id.Equals(backdrop.Id)); }
                if (backdropChild != null) { elements = elements.Where(item => item.SubFondo.Id.Equals(backdropChild.Id)); }
                if (section != default(SeccionesSeccion)) { elements = elements.Where(item => item.SeccionTRD.Id.Equals(section.Id)); }
                if (subsection != default(SeccionesSeccion)) { elements = elements.Where(item => item.SubseccionTRD.Id.Equals(subsection.Id)); }
            }

            exportData = new DataTable();
            exportData.Columns.Add("D");
            exportData.Columns.Add("S");
            exportData.Columns.Add("SB");
            exportData.Columns.Add("Serie");
            exportData.Columns.Add("Subserie");
            exportData.Columns.Add("Tipos Documentales");
            exportData.Columns.Add("AG");
            exportData.Columns.Add("AC");
            exportData.Columns.Add("C");
            exportData.Columns.Add("MT");
            exportData.Columns.Add("SE");
            exportData.Columns.Add("E");
            exportData.Columns.Add("Observaciones");


            foreach (var entity in elements)
            {
                exportData.Rows.Add(new object[] 
                    { 
                        entity.SeccionTRD.Código, entity.SerieTRD.Código, entity.SubserieTRD.Código, entity.SerieTRD.Title, entity.SubserieTRD.Title, 
                        entity.TiposDocumentales.ToCSVString(), entity.TiempoDeRetenciónEnArchivoGeneral, entity.TiempoDeRetenciónEnArchivoCentral, entity.Conservación
                        , entity.MedioDeConservación, entity.Selección, entity.Eliminación, entity.Legislación 
                    });
            }

            return exportData;
        }

    }

}
